Note: The Apama Database Connector (ADBC) is deprecated and will be removed in a future release.
The Apama Database Connector (ADBC) is an adapter that uses the Apama Integration Adapter Framework (IAF) and connects to standard ODBC and JDBC data sources as well as to Apama Sim data sources. With the ADBC adapter, Apama applications can store and retrieve data in standard database formats as well as read data from Apama Sim files. Data can be retrieved using the ADBCHelper API or the ADBC Event API to execute general database queries or retrieved for playback purposes using the Apama Data Player.
There are three versions of the ADBC adapter, one each for ODBC, JDBC, and Sim data sources.
ADBC is implemented as an Apama adapter that uses the Apama Integration Adapter Framework (IAF) to connect to standard ODBC and JDBC data sources as well as to Apama Sim data sources.
When connected to JDBC or ODBC data sources, ADBC provides access to most open source and commercial SQL databases. With either of these data sources, Apama applications can capture events flowing through the correlator and play them back at a later time. In addition to storing and retrieving event data, Apama applications can store non-event data and execute queries against the data. Dashboards in Apama applications can directly access JDBC database data.
An Apama Sim data source is a file with data stored in a comma-delimited format with a .sim file extension. Apama release 4.1 and earlier captured streaming data to files in this format. The Apama ADBC adapter can read .sim files but it does not store data in that format. For information on the format of .sim files, see Format of events in.sim files.
Apama provides JDBC database drivers for the following Apama-certified databases:
Microsoft SQL Server
Oracle
Apama does not provide any ODBC drivers. You need to use your own ODBC drivers to use ODBC. Any bugs in driver need to be directly resolved with the driver vendor. Use of JDBC rather than ODBC is recommended.
Using the Apama database drivers eliminates the need to install vendor-supplied drivers. In addition, they are pre-configured; so when you select an Apama database driver in an Apama project in Apama Plugin for Eclipse, the adapter instance is automatically configured with appropriate JDBC settings.
The Apama JDBC drivers are licensed to be used with any Apama component.
Apama provides two Application Programming Interfaces (APIs) for using the ADBC Connector: the ADBCHelper API and the ADBC Event API.
The ADBCHelper API contains the basic features you need for most common use cases, such as opening and closing databases and executing SQL commands and queries. For more information on the ADBCHelper API, see The ADBCHelper application programming interface.
The ADBC Event API contains features for more complex use cases. For example, in addition to opening and closing databases, it contains actions for discovering what data sources and databases are available. For more information on the ADBC Event API, see The ADBC Event application programming interface.
Apama’s ADBC Adapter editor in Apama Plugin for Eclipse includes an Event Mapping tab that lets you quickly specify the mapping rules for storing events in existing database tables. Apama Plugin for Eclipse generates a service monitor that listens for the events of interest and stores them in the database. This monitor provides a quick and straight forward way of writing event data to a database for general analytical purposes; however, it is not meant to be a fail-safe management system.
The ADBC adapter uses separate thread pools for executing queries and commands and will execute each command and query in its own thread. The thread pools are created with a minimum of four threads but for machines with more than four CPU cores the number of threads will match the number of cores. The adapter log will show the number of threads in the thread pools, for example:
Query and Command threadpools using 4 threads
The maximum number of concurrent queries running will match the number of threads in the thread pool. As an example, on a machine with less than four cores, this would be four concurrent queries and four concurrent commands.
Additional queries and commands submitted will be queued for execution until a thread becomes free. If more than four long running queries are submitted, additional queries will be queued. If a mix of short and known long running queries are being used, the application may want to control the submission of long running queries to ensure the shorter duration queries do not have to wait. If the execution of the short duration queries are required to be run without delay, a second adapter can also be started and used to service just the shorter duration queries.
Registering your ODBC database DSN on Windows
On Windows it is necessary to register your database and give your database configuration a unique Data Source Name (DSN) before using it from Apama.
To register your ODBC database DSN
From the Windows Control Panel, double-click the ODBC Data Sources icon. If this icon is not listed, double-click the Administrative Tools icon and then double-click the Data Sources (ODBC) icon.
This will open the ODBC Data Source dialog.
On the User DSN tab, click Add.
In the Create New Data Source window, select the driver for which you want to setup a data source.
Click Finish to display the Setup dialog.
Enter a Data Source Name. This is the name you will use in Apama when creating data attachments.
Click OK in the Setup, and ODBC Data Source dialogs.
Note: Standard UNIX systems do not provide an ODBC driver. On UNIX systems, it is currently unsupported to set up an ODBC driver to communicate with your database.
Adding an ADBC adapter to an Apama project
When you add an ADBC adapter to an Apama project in Apama Plugin for Eclipse, all the resources associated with the adapter such as service monitors and configuration files are automatically included.
ADBC adapters are available for three different data sources:
JDBC Adapter (Apama database connector for JDBC)
ODBC Adapter (Apama database connector for ODBC)
Sim File Adapter (Apama database connector for Sim files)
To add an adapter to a project
There are two ways of adding an ADBC adapter to a project.
If you are creating a new Apama project:
From the File menu, choose New > Apama Project.
Give the project a name, and click Next.
If you are adding an ADBC adapter to an existing project:
In the Project Explorer view, right-click the Connectivity and Adapters node and select Add Connectivity and Adapters.
Enter a new name for the adapter instance or accept the default instance name. Apama Plugin for Eclipse prevents you from using a name that is already in use.
Select the ADBC adapter bundle that is appropriate to the kind of data source your application will use. Click OK.
When you add a data source-specific adapter, the ADBC Adapter (Common Apama database connector adapter) bundle will be added to the project automatically.
Configuring the Apama database connector
The Apama Database Connector is an adapter that is instantiated with the Apama Integration Adapter Framework (IAF). The IAF enables Apama applications to connect to sources of messages and events and to consumers of messages and events; with ADBC, these sources and consumers can be databases. Before using the ADBC adapter, you need to supply the correct information in the adapter’s configuration file.
If you develop your Apama application using Apama Plugin for Eclipse, the correct configuration files are included in the application’s project file when you add the appropriate ADBC adapter bundle to the project. In order to connect to a database, you need to specify in the adapter’s configuration file the properties such as the type and name of data source and the name of the database that the application will use.
If you are not using Apama Plugin for Eclipse, you need to manually create the configuration file from the ADBC adapter template file. For more information on creating the configuration file manually, see Manually editing a configuration file.
Configuring an ADBC adapter
In Apama Plugin for Eclipse, an adapter’s configuration file is opened in Apama’s adapter editor. By default, the file is displayed in the editor’s graphical view, which is accessed by clicking the Settings tab. The editor’s other tabs are:
Event Mapping — Displays the Visual Event Mapper where you can quickly map Apama event fields to columns in a database table.
XML Source — Displays the configuration file’s raw XML code.
Advanced — Provides access to other configuration files associated with the adapter instance. These other files specify, for example, the instance’s mapping rules, generated monitors and events responsible for storing events in a database, and named queries.
To configure an instance of an ADBC adapter
In the Project Explorer, expand the project’s Adapters node and open the adapter folder (ODBC Adapter, JDBC Adapter, or Sim File Adapter).
Double-click the entry for the adapter instance you want to configure. The configuration file opens in the adapter editor.
The Settings tab of the editor’s graphical display presents configuration information. For an instance of the ADBC-JDBC adapter, the following tabs are shown:
General Properties
Advanced Properties
Variables
For an instance of the ADBC-ODBC adapter, the display is similar but with fewer items in the above sections. For an instance of the ADBC-Sim file adapter, the display only shows the Variables section.
In the General Properties section, add or edit the following:
Database type — This drop-down list allows you to select one of the database types from the list of certified vendors.
Database URL — This specifies the complete URL of the database. By default, it uses the value of the DATABASE_LOCATION variable; for more information on this variable, see the description of the Variables section below.
Driver — For the ADBC-JDBC adapter, this specifies the class name of the vendor’s JDBC driver. By default, it uses the value of the JDBC_DRIVER_NAME variable; for more information on this variable, see the description of the Variables section below.
Driver classpath — For the ADBC-JDBC adapter, this specifies the classpath for the vendor’s driver jar file. By default, it uses the value of the JDBC_DRIVER_JARFILE variable; for more information on this variable, see the description of the Variables section below.
Store batch size — This defines the number of events (rows) to persist using the ODBC/JDBC batch insert API. The use of this setting will significantly increase store performance, but it is not supported by all drivers. A value of 100 is appropriate and will provide good performance in most cases.
If store performance is critical, testing is required to find the optimal value for the data and driver being used. The default is 0 which disables the use of batch inserts.
Store commit interval — This defines the interval in seconds before the ADBC adapter will automatically perform a commit for any uncommitted SQL command or store operations. The default value is 0.0, which disables the use of the timed commits.
Auto commit — This controls the use of the ODBC/JDBC driver autocommit flag. The default value is false.
Login timeout — This is a JDBC-specific property that allows you to change the default login timeout when com.apama.database.Connection.OpenDatabase or com.apama.database.Connection.OpenDatabaseShared are called.
Query timeout — This is a JDBC-specific property that allows you to set the timeout for queries. The default value is 0. Keep in mind that different database vendors define a query timeout differently; see the documentation for these databases for more information.
Note:
For more information on the interaction of the Auto commit, Store commit interval and Store batch size properties, see Committing database changes.
In the Advanced Properties section, add or edit information for the following:
Transaction isolation level — This specifies what data is visible to statements within a transaction. The Default level uses the default level defined by the database server vender. To change this setting, enter the appropriate value. For JDBC and ODBC, the values can be READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, or SERIALIZABLE.
Alternate discovery query — In most situations, an entry here is not required and the ADBC Discovery method lists the database available based on the DATABASE_LOCATION variable. In some cases, you may need to use a server vendor-specific SQL query statement to list the available databases, such as MySQL’s SHOW DATABASES.
Log inbound events — A boolean that specifies whether or not the application logs inbound ADBC API events with information such as the exact query or command being executed. Logging these events is used for diagnostic purposes and eliminates the need to turn on IAF debug logging. The default is false; do not log incoming events.
Log outbound events — The same as Log inbound events except for outbound ADBC API events.
Log commands — This property specifies whether or not the starts and completions of commands are written to the IAF log file. A value of true (the default) logs this information; a value of false turns logging off. This is useful in cases where logging the start and completion of a high rate of commands (many hundreds or thousands per second) does not add usable information to the log file.
Log queries — This property behaves identically to the Log commands property except that it specifies whether or not to log the start and completion of queries.
Flow control low water — This defines a threshold for the number of query responses not acknowledged by the ADBC flow control monitor before a query paused by Flow control high water is resumed. This is used by the ADBC query flow control system to ensure the correlator does not get overwhelmed, especially when performing a fast as possible playback. The default value is 6000.
Flow control high water — This defines a maximum threshold for the number of query responses that have not been acknowledged by the ADBC flow control monitor. If this value is reached, the query will be paused until the number of outstanding acknowledgments decreases to the Flow control low water value. This is used by the ADBC query flow control system to ensure the correlator does not get overwhelmed, especially when performing a fast as possible playback. The default value is 15000.
Query template config file — This specifies the file containing the query templates that are available to the application. By default, this uses a default template file created for the individual Apama project.
You can add or edit values of the following additional advanced properties by clicking the XML Source tab and modifying the text of the configuration file:
NumericSeparatorLocale — This allows the numeric separator used in the adapter to be changed, if necessary, to match the one used by the correlator. See Configuring ADBC localization.
FixedSizeInsertBuffers — This is an ODBC-specific property that allows you to change the default buffer size used when the StoreData and StoreEvent actions perform batch inserts. Apama uses the FixedSizeInsertBuffers property along with the StoreBatchSize property to determine how large the insert buffers should be. The value specified by StoreBatchSize determines how many rows need to be buffered; the value specified by FixedSizeInsertBuffers controls the size of the buffers for the columns. The default true uses a fixed buffer size of 10K bytes for each column. If the value is changed to false, the size of the column buffers is determined dynamically by examining the database table into which the data will be inserted. Allowing the buffer size to be set dynamically can significantly reduce memory usage when performing batch inserts to database tables that contain hundreds of columns or when using a very large StoreBatchSize.
In the Variables section, add or edit the appropriate values for the following tokens:
ADAPTER_CONFIG_DIR — JDBC and ODBC adapters only. This specifies the directory where the adapter’s configuration files are located. This is automatically set by default.
ADAPTER_INSTANCE_ID — This refers to the instance ID that is given to the adapter by Apama Plugin for Eclipse.
ADAPTER_INSTANCE_NAME — This refers to the adapter name that is displayed in Apama Plugin for Eclipse.
ADAPTERS_DIR — JDBC and ODBC adapters only. Specifies the directory where the adapter will look for the adapter files. By default, this is the Apama installation’s adapters directory.
ADAPTERS_JARDIR — JDBC adapter only. This specifies the directory where the Apama adapter jar files are located. By default, this is the Apama installation’s adapters\lib directory.
APAMA_HOME — This refers to the location of the Apama installation directory.
APAMA_MSG_ENABLED — Apama elements can be enabled to define how IAF connects to the Apama correlator(s). The enabled attribute has valid values as true or false. This is based on the launch configuration.
BUNDLE_DIR — Sim file adapter only. This specifies the directory where the adapter bundle is located.
BUNDLE_DISPLAY_NAME — This refers to the adapter bundle name that is displayed in Apama Plugin for Eclipse.
BUNDLE_INSTANCES — Sim file adapter only. This refers to the bundle instances files that Apama Plugin for Eclipse has copied to the project.
CORRELATOR_HOST — This specifies the name of the host machine where the project’s default correlator runs. This is automatically set by default.
CORRELATOR_PORT — This specifies the port used by the correlator. This is automatically set by default.
DATABASE_LOCATION — This specifies the location of the database for use with the ADBC Discovery API, for example, jdbc:mysql://localhost/trades.
JDBC_DRIVER_JARFILE — JDBC adapter only. This specifies the name of the data source driver file, for example, a MySQL version X driver path might be specified at C:/Program Files/MySQL/mysql-connector-java-X/mysql-connector-java-X.jar.
JDBC_DRIVER_NAME — JDBC adapter only. This specifies the class name of the driver, such as com.mysql.jdbc.Driver.
MAPPING_INSTANCE_FILE — JDBC and ODBC adapters only. This refers to the file name for the adapter’s mapping configurations when configured through Apama Plugin for Eclipse.
Use of Universal Messaging from the IAF is deprecated and will be removed in a future release.
UM_MSG_ENABLED — This refers to the flag that indicates whether the IAF is configured to use Universal Messaging.
Note:
Use of Universal Messaging from the IAF is deprecated and will be removed in a future release.
Specify the event mapping rules of the configuration that are specific to your application using the adapter editor’s Visual Event Mapper, available on the Event Mapping tab. For more information on specifying mapping rules, see The Visual Event Mapper.
Manually editing a configuration file
If you are not using Apama Plugin for Eclipse to develop your Apama application, you need to manually copy the correct template files to your development environment. The Apama installation provides template files to use as the basis for creating the IAF configuration file to start the ADBC adapter. The templates are located in the adapters\config directory of the Apama installation. The following templates are available:
ADBC-Sim.xml.dist — Use this configuration file template for accessing a Sim data source.
ADBC-ODBC.xml.dist — Use this configuration file template for accessing an ODBC data source.
ADBC-JDBC.xml.dist — Use this configuration file template for accessing a JDBC data source.
To create the configuration file for starting the ADBC adapter
Copy the appropriate template to your project.
Edit the name attributes of the various transport properties as necessary.
When you start the IAF with the modified configuration file using the syntax iaf path_to_modified_config_file, it automatically includes the appropriate common configuration files shown below.
ADBC-static.xml — Common event mapping for the ADI adapter events.
ADBC-static-codecs.xml — The codecs to use (currently null-codec).
ADBC-application.xml — Application specific event mappings.
ADBC-namedQuery-Sim.xml — The named query definitions for a Sim data source.
or
ADDC-namedQuery-SQL.xml — The named query definitions for ODBC and JDBC data sources.
ADBC-mapping_*instance\_name*.xml — Contains the mappings defined by the user using the Visual Event Mapper.
Configuring ADBC localization
The ADBC adapter internally handles all string data as UTF-8, and provides the same internationalization support as the correlator. The correlator internally uses the C programming language locale for formatting string versions of numeric values, so there can be conditions under which the ODBC and JDBC drivers may use a locale that is not compatible with the English numeric separator format used in ADBC. In locales that do not use English numeric separators, the ODBC and JDBC drivers for some SQL vendors may not correctly handle numeric values passed from the correlator. To address these cases, the ADBC adapter configuration property NumericSeparatorLocale allows the numeric separator used in the adapter to be changed to match the one used by the correlator. The property can be set to one of three values:
Native: Set numeric separator format to system default.
A value of C causes the adapter’s numeric separator locale to match that used by the correlator, so that the JDBC and ODBC drivers correctly handle the numeric values. The value Native causes the adapter to set the locale to the system default. This value is not generally needed and was added for future use and for special cases in which technical support would direct it to be set. If you notice incorrect numeric values when inserting or querying data from the database when running in a locale that doesn’t use the English-style numeric separators, then changing the NumericSeparatorLocale property to C should correct the problem. In Apama Plugin for Eclipse, you can access this property by using the XML Source tab in the adapter editor.
Configuring ADBC Automatic Database Close
The ADBC adapter performs a connectivity check when a JDBC or ODBC error is encountered, and can be configured to automatically perform the database close operation if a connection is found to be invalid. The IAF status manager will detect the database connection has been closed and report the change in connection status. Applications need to monitor the database connection status in order to take advantage of the automatic closing; this functionality is not integrated into the ADBC APIs.
The ADBC adapter configuration property CloseDatabaseIfDisconnected is used to enable the closing of databases that are detected as invalid.
False: Default. Don’t perform automatic closing.
True: Close databases detected as invalid (that is, disconnected).
Service monitors
If your Apama application uses ADBC, you need to inject several required service monitors. In Apama Plugin for Eclipse, this is done automatically when you add the appropriate data source adapter bundle to the application’s project as described in Configuring the Apama database connector. If you are not using Apama Plugin for Eclipse to develop your application, you need to manually inject the following required service monitors in the order they are listed:
ADBCAdapterEvents.mon — Provides definitions for all events sent to or from the ADBC Adapter.
ADBCEvents.mon — Provides the public API for ADBC, implemented as actions on the following events:
Discovery — This event types defines the actions for discovering ADBC resources. It is used to find the available data sources (ODBC, JDBC, Sim, etc.) and the default databases and query templates configured for those data sources.
Connection — This event type defines actions for performing all operations on a database except those involving queries
Query — This event type defines actions for performing queries on a database.
ADBCAdapterService.mon — Provides actions for the following:
Forwarding database request events to the adapter.
Forwarding database response events to the ADBC Service API layer.
Provides support for using the ADBC event API from contexts other than the main context (see also setPrespawnContext in Setting context).
IAFStatusManager.mon
StatusSupport.mon
ADBCStatusManager.mon — Manages status subscriptions for the ADBC adapter and the application.
ADBCHelper.mon — Include this monitor for applications that use the ADBCHelper API.
The ADBC monitors and IAFStatusManager.mon are located in the adapters\monitors directory of the Apama installation. The StatusSupport.mon monitor is located in the Apama installation’s monitors directory
Codecs
By default, the ADBC adapter uses the standard Apama NullCodec. During playback, if your application needs to modify, aggregate or perform analytics on events, you can create and specify IAF codecs to perform these operations instead of using the standard NullCodec. For example, capital market applications might convert quote to depth events during playback from a market database. You define the logic for performing this type of conversion in the codec.
The ADBCHelper application programming interface (API) is a simplified, streamlined API for communicating with databases. In most common use cases, this API is the appropriate way to develop applications. For applications that require more complex ways of accessing databases, see The ADBC event application programming interface.
ADBCHelper API overview
The ADBCHelper API is defined in the file apama_dir\adapters\monitors\ADBCHelper.mon. The API is implemented with the following events:
com.apama.database.DBUtil
com.apama.database.DBAcknowledge
The DBUtil event defines the actions that Apama applications call in order to interact with databases. The DBAcknowledge event is used by the ADBCHelper API to specify the success or failure for database actions that request an acknowledgment. Note if you specify the following lines in your code, you do not need to use the fully qualified name for DBUtil or DBAcknowledge.
using com.apama.database.DBUtil;
using com.apama.database.DBAcknowledge;
The basic steps for using the ADBCHelper API are described below.
To use the ADBCHelper API
Create an instance for the DBUtil event in your application code, for example:
com.apama.database.DBUtil db;
Call the setAdapterInstanceName action of DBUtil to identify the adapter instance, for example:
Check whether the database is already open or is in the process of being opened. This step is optional, but it is good programming practice to check for these situations before calling an open event action by calling the isOpen action of DBUtil. This returns a boolean that specifies if the database is already open or in the process of being opened. For more information, see Checking to see if a database is open.
Call one of the DBUtil open actions to open the database. For more information on open actions, see Opening databases.
Call one or more DBUtil event actions, depending on the database task you want to implement:
Call a SQL query event action to retrieve data from the database, in either a result set or in Apama event format. For more information on query actions, see Issuing and stopping SQL queries.
Call a SQL command event action to add, update, or delete data in the database. For more information on SQL command actions, see Issuing SQL commands.
Optionally, if the autoCommit setting has been turned off, call a commit event action to commit database changes, or call a rollback event action to rollback uncommitted changes.
Create actions as required to handle returned result sets. If the query returns events, create listeners for events returned by the query. For more information on handling query results, see Handling query results for row data.
For action calls that request an acknowledgment, your application needs to do the following:
Create an instance of the com.apama.database.DBAcknowledge event.
Note, if your code contains the following line, you do not need to use the fully qualified name for DBAcknowledge.
using com.apama.database.DBAcknowledge;
Create a listener for the DBAcknowledge event that indicates when the DBUtil event action call is complete.
Create an action that handles errors that could occur during execution of a DBUtil event action call. For more information, see Handling errors.
Call the DBUtil event’s close action to close the database. For information, see Closing databases.
Opening databases
The ADBCHelper API provides several actions for opening databases. The “quick” open actions allow you to open JDBC and ODBC databases by passing in a minimal set of parameters, while the “full” open action provides more control by passing in a complete set of parameters. The “shared” open action allows you to use an already open existing matching connection or open a new connection if a matching one does not exist.
In the following quick open actions, you need to pass in values for the following parameters:
URL — database connection string
user — user name
password — user password
handleError — name of a default error handler
See Handling errors for more information on creating actions to handle errors.
The quick open actions use the default settings for the autoCommit (true), batchSize (100), and timeOut (30.0) properties.
Setting the autoCommit, batchSize, and timeOut parameters in the open action over-rides the adapter properties specified in the IAF configuration file.
type — The data source type (ODBC, JDBC, Sim, etc.)
serviceId — The service id for the adapter
URL — The database connection string
user — The user name
password — The user password
autoCommit — The auto commit mode to use. If this parameter is not set, the open action uses a combination of the AutoCommit and StoreCommitInterval properties specified in the adapter’s configuration file. For information on these properties, see Configuring an ADBC adapter. The value for the autoCommit parameter can be one of the following modes:
"" — An empty string specifies that the value set in the configuration file should be used.
true — Enables the ODBC/JDBC driver’s auto commit.
false — Disable autoCommit.
x.x — Use timed auto commit interval in seconds.
For more information on the interaction of the AutoCommit, StoreCommitInterval and StoreBatchSize properties, see Committing database changes.
readOnly — Specifies if the connection should be read-only. If the connection is read-only an error will be reported for any API action that requires writes (Store, Commit, or Rollback). Most databases do not prevent writes from a connection in read-only mode so it is still possible to perform writes using the Command actions.
batchSize — The query results batch size to be used for any queries performed.
timeOut — Controls how long the ADBC open action will wait for the adapter to become available if it is not running when the open action is called.
The following code snippet shows a use of the open action. It creates variables for each of the parameters and passes them with the open action.
The following open action allows you to use a connection that is already open; the action opens a connection if an existing matching connection is not found. The openShared action uses the same parameters as the open action, above.
Checking to see whether the database is already open or is in the process of being opened before calling an open event action is optional, but it is good programming practice. An application may also want to check if a database is open before executing a query.
The following example checks these fields to ensure that the application does not try to open an already opened database.
com.apama.database.DBUtil db;
//...
if not db.isOpen() {
db.openQuickODBC(dbUrl,"","",handleError );
}
Issuing and stopping SQL queries
The following actions execute SQL queries. The actions expect a response and a handleResult action needs to be defined to handle each row returned.
The following query action allows you to specify a callback action for when the query completes. The parameters are (1) the query string, (2) the handler action for each row returned and (3) the handler for when the query completes. The handler for when the query completes has two parameters, an error string and an integer that specifies the number of rows returned by the query.
The doSQLCmdOnError action executes only if a previous non- *OnError operation failed. This is useful for doing, for example, a select * from table command and then, if an error occurs, execute a create table... command.
Committing database changes
There are several approaches to defining when ADBC should commit changes. Although it is possible to use more than one of these, it is usually best to stick to just one:
EPL-controlled committing by calling DBUtil.doSQLCommit (or doSQLCommitAck). This is the recommended approach for applications that update the database, as it gives maximum control of transactions to the EPL application. Do not use this when autoCommit is enabled.
Automatic committing per SQL statement by setting autoCommit=true, which configures the underlying database driver to automatically perform a commit after the completion of each SQL statement (by default, this is disabled). This is useful for simple applications that only perform read-only queries.
Timed committing by setting commitStoreInterval, which configures ADBC to batch up SQL statements over the specified time window into a single commit (the default is 0 which means that timed committing is disabled). Do not use this when autoCommit is enabled.
Note: Whether you are using queries to get data from the database or to put data in, it is essential to use at least one of these commit mechanisms, as queries will not complete until a commit is issued.
There is also a property called StoreBatchSize which allows multiple SQL statements to be batched together into a single call to the database, in order to reduce communication overhead. The batching of statements does not itself result in any extra commits, but does still have some impact on which statements are committed together:
If using EPL-controlled committing, the partial batch that has been received so far will be committed.
If autoCommit is enabled, statements are not committed until the batch size is reached.
If using timed committing, a partial batch is committed when the time expires based on what has been uncommitted to that point.
Performing rollback operations
For rolling back uncommitted changes to database, use the following DBUtil actions. If you want to use rollback actions, you need to turn autocommit off.
action doSQLRollback()
For rolling back uncommitted changes to database in situations where the previous SQLCmd, SQLQuery, or SQLCommit operation failed, use:
action doSQLRollbackOnError()
When you want to rollback uncommitted changes to the database and receive a DBAcknowledge event to indicate success or failure, use:
The *ackNum* parameter is the identifier for the DBAcknowledge event; setting it to -1 will disable sending the DBAcknowledge event and instead use the default error handler if an error occurs. For the *onError* parameter, setting its value to true will cause the operation to run only if the previous SQLCmd, SQLQuery, or SQLCommit failed.
Handling query results for row data
For query actions that return a result set of rows of data, your application needs to define actions to handle result sets. For example:
For query actions that return a result set in the form of events, your application needs to do the following.
Define an event type that represents the returned data.
Map the returned data to fields in the event type. The easiest way to do this is to use the Apama’s Visual Mapper in Apama Plugin for Eclipse, which automatically saves the mapping information in a project file. For more information, see Using the Visual Event Mapper.
Create a listener for the event type.
Execute a query that returns events.
For example, the following EPL code snippet defines an event, executes a query that returns data in the form of the defined event, and defines a listener for the defined event:
event NetworkInfo {
string network;
integer countrycode;
integer nid;
}
//...
com.apama.database.DBUtil db;
action onload() {
db.openQuickODBC("exampledb","thomas","thomas123",handleError);
db.doSQLEventQuery("SELECT * FROM network_info", NetworkInfo);
//...
}
on all NetworkInfo := netInfo {
// Code to do something with the returned event...
}
Handling acknowledgments
Apama applications can call DBUtil SQL command and query actions as well as commit and rollback actions that request a DBAcknowledgement event. The DBAcknowledgement event indicates the success or failure of the action call. This is useful, for example, to know whether or not a query has completed before performing another application operation.
The DBAcknowledgement event is defined in apama_install_dir\adapters\monitors\ADBCHelper.mon as follows:
ackNum — A unique identifier for the action that requested the acknowledgment.
success — A value of true indicates success; false indicates failure.
error — A string describing the specific error.
For action calls that request an acknowledgment, your application needs to do the following:
Call an action that requests an acknowledgment, passing in a unique acknowledgment identifier.
Create a listener for theDBAcknowledge event that matches the acknowledgment identifier in the calling action.
For example:
integer ackId := integer.incrementCounter("ADBC.ackId");
db.doSQLQueryAck("SELECT * FROM NetworkInformation",
handleNetworkInfo,ackId,false);
//...
on DBAcknowledge(ackNum = ackId) as ack {
if ack.success {
log "Query complete" at INFO;
}
else {
log "Query failed: " + ack.error at ERROR;
die;
}
}
//...
Handling errors
The DBUtil actions require a user-defined handleError() action that takes a single string parameter. The handleError() action handles errors that could occur during execution of a DBUtil event action call.
The following EPL code snippet shows a simple error handler.
Apama applications can automatically reconnect if a disconnection error is encountered. The reconnection capability is optional and the default is to not reconnect when a disconnection error occurs. The following reconnection actions are defined in the com.apama.database.DBUtil event.
action setReconnectPolicy(string reconnectPolicy) — This action sets the policy for dealing with adapter connection errors. The *reconnectPolicy* parameter must be one of the constants specified in the DBReconnectPolicy event. The policy constants are as follows:
RECONNECT_AND_RETRY_LAST_REQUEST — Try to reconnect and leave the pending requests unchanged, retry the last request on a successful database reconnection.
DO_NOT_RECONNECT — Do not try to reconnect. This is the default reconnect policy.
action setReconnectTimeout(float timeOut) — This action sets the timeout for the reconnection after a connection error. A value specified by the setReconnectTimeout action overrides the default timeout value, which is equal to twice as long as specified by the open action’s *timeOut* parameter.
Closing databases
The following action closes the database. If doStopAll is set it also cancels all outstanding queries and commands in the queue and prevents new queries and commands from being placed into the queue.
action close( boolean doStopAll )
Getting schema information
The following actions return information about a table in a database. The actions are only valid in the handleResult action specified in a doSQLQuery, doSQLQueryOnError, or doSQLQueryAck operation when dealing with a returned row.
By default the ADBCHelper API sends requests to an internal service monitor running in the main context with the EPL route statement. However, if your application uses parallel processing and spawns to multiple contexts, you have to add code that identifies the main context so the ADBCHelper API can determine how to send events.
In applications with multiple contexts, use the following action to specify the main context before spawning.
setPrespawnContext( context c )
Logging
This action specifies whether or not to log all SQL queries, commands, and commit operations to the correlator’s log file.
action setLogQueries( boolean logQueries )
The default is false, which disables logging.
The ADBC Event application programming interface
The ADBC (Apama Database Connector) Event application programming interface (API) provides operations for more complex, lower level interactions with databases than the operations included with the ADBCHelper API. The ADBC Event API is implemented with the following Apama event types and actions associated with those events.
Discovery — This event type provides actions to obtain the names of data sources, databases, and named queries. Discovery actions are not necessary if your application knows the names of data sources, databases, and query templates.
Connection — This event type provides actions for all operations on a database except for those involving queries.
Query — This event type provides actions for creating and executing queries on databases.
PreparedQuery — This event type provides actions for creating prepared query statements that are, in turn, used in queries.
The above events and associated actions are defined in the ADBCEvents.mon file.
In addition, some of the actions for Discovery events use the following event types, which are defined in the ADBCAdapterEvents.mon file.
DataSource
Database
QueryTemplate
Discovering data sources
If your application needs to find available data sources, implement the following steps.
To discover data sources
Create a new Discovery event.
Use the Discovery event’s findAvailableDataSources action.
Create a handler action to perform callback actions on the results of the findAvailableDataSources action.
In the handler action, declare a variable for a DataSource event.
The definitions for the two forms of the findAvailableDataSources action are:
serviceId — The service ID to talk to this DataSource.
name — The name of the DataSource such as ODBC, JDBC, or Sim.
extraParams — Optional parameters.
The relevant code is similar to this:
com.apama.database.Discovery adbc :=
new com.apama.database.Discovery;
adbc.findAvailableDataSources(TIME_TO_WAIT, handleAvailableServers);
action handleAvailableServers(string error,
sequence<com.apama.database.DataSource> results)
{
if error.length() != 0 {
log "Error occurred getting available data sources: " +
error at ERROR;
}
else {
if results.size() > 0 {
// Save off first service ID found.
// Assumes first data source has at least one db
if getDbServiceId() = "" {
dbServiceId := results[0].serviceId;
}
com.apama.database.DataSource ds;
log " DataSources: " at INFO;
for ds in results {
log " " + ds.name + " - " + ds.serviceId at INFO;
}
log "Finding Databases..." at INFO;
//... other logic...
}
else {
log " No DataSources found" at INFO;
}
}
}
Discovering databases
If your application needs to find available databases, implement the following steps.
To discover databases
Given a Datasource event, call the event’s getDatabases action.
Create a handler action to perform callback actions on the results of the getDatabases action.
In the handler action, declare a variable for a Database event.
The definitions for the two forms of the getDatabases action are:
dbUrl — The complete URL of the database, for example, “jdbc:sqlserver://localhost/ApamaTest”.
extraParams — Optional parameters.
The relevant code is similar to this:
action handleAvailableServers(string error,
sequence<com.apama.database.DataSource> results)
{
if error.length() != 0 {
log "Error occurred getting available data sources: " +
error at ERROR;
}
else {
if results.size() > 0 {
// Save off first service ID found.
// Assumes first data source has at least one db
if getDbServiceId() = "" {
dbServiceId := results[0].serviceId;
}
com.apama.database.DataSource ds;
log " DataSources: " at INFO;
for ds in results {
log " " + ds.name + " - " + ds.serviceId at INFO;
}
log "Finding Databases..." at INFO;
for ds in results {
adbc.getDatabases(ds.serviceId, USER, PASSWORD,
handleAvailableDatabases);
}
}
else {
log " No DataSources found" at INFO;
}
}
}
string dbName;
action handleAvailableDatabases(string error,
sequence<com.apama.database.Database> results)
{
if error.length() != 0 {
log "Error occurred getting available databases: " +
error at ERROR;
}
else {
if results.size() > 0 {
// Save name of first db found
if getDbName() = "" {
dbName := results[0].shortName;
}
com.apama.database.Database db;
log " Databases: ";
for db in results {
log " " + db.shortName + " - " +
db.description + " - " + db.dbUrl at INFO;
}
//... other logic...
}
else {
log " No Databases found" at INFO;
}
}
}
Opening a database
In order to open a database, your application should implement the following steps:
Create a new Connection event.
Call the Connection event’s openDatabase action with the database’s service ID, database URL, autocommit preference, and the name of the callback action.
Create the handler action for the openDatabase callback action.
The definitions for the different forms of the openDatabase actions are:
In addition to these open actions you can also open a database using an already open matching connection if one exists using the openDatabaseShared action. If an existing connection is not found, the action opens a new connection.
The value for the autocommit parameter is a combination of the AutoCommit and StoreCommitInterval properties. For information on these properties, see Configuring an ADBC adapter. The value for the autocommit parameter can be one of the following modes:
"" — An empty string specifies that the value set in the configuration file should be used.
true — Use the data source’s value as determined by the ODBC or JDBC driver.
false — Disable autocommit.
x.x — Use time auto commit interval in seconds.
The readOnly parameter specifies if the connection should be read-only. If the connection is read-only an error will be reported for any API action that requires writes (Store, Commit, or Rollback). Most databases do not prevent writes from a connection in read-only mode so it is still possible to perform writes using the Command actions.
Specifying parameter values in the open actions overrides the property values set in the configuration file.
The relevant code is similar to this:
com.apama.database.Connection conn :=
new com.apama.database.Connection;
action handleAvailableDatabases(string error,
sequence<com.apama.database.Database> results)
{
if error.length() != 0 {
log "Error occurred getting available databases: " +
error at ERROR;
}
else {
if results.size() > 0 {
// Save name of first db found
if getDbName() = "" {
dbName := results[0].shortName;
}
com.apama.database.Database db;
log " Databases: " at INFO;
for db in results {
log " " + db.shortName + " - " +
db.description + " - " + db.dbURL at INFO;
}
log "Opening Database " + dbName + "..." at INFO;
string serviceId := getDbServiceId();
conn.openDatabase(serviceId, results[0].dbUrl, USER,
PASSWORD, "", handleOpenDatabase);
}
else {
log " No Databases found" at INFO;
}
}
}
Note:
If reusing a database connection, rather than calling openDatabase again, it is advised to use reopenWithAck instead. In cases where there are issues using the current connection, for example, the call to closeDatabase is not succeeding following an incident where the IAF went down, then you should call reopenWithAck to recover the connection.
Closing a database
In order to close a database your application should implement the following steps:
Call the closeDatabase() action of the Connection event (for the open database) with the name of the callback action.
Create a handler action for the closeDatabase callback action.
The definitions for the two forms of the closeDatabase() action are:
In order to store an event in a database, your application needs to use the Connection event’s storeEvent action. The definition of the storeEvent action is:
The getTime() call on the event is used to set the timestamp value.
Similarly, the toString() call on an event sets the eventString field.
The tableName parameter specifies the name of the database table where you want to store the data.
The statementName parameter specifies the name of a storeStatement that references a prepared statement or stored procedure. The storeStatement is created with the Connection event’s createStoreStatement action. See Creating and deleting store events for more information on creating a storeStatement. If you do not want to specify a prepared statement or stored procedure, the statementName parameter should be set to "" (an empty string).
The timeColumn parameter specifies the column in the database where you want the event timestamp to be stored.
The storeEvent action returns an integer value, which is the identifier for the event being stored. The setStoreErrorCallback action is used to specify an action to be used when an error is reported.
To store an event and provide acknowledgment, implement the storeEventWithAck() action and a callback handler. The definition of the storeEventWithAck action is:
In addition to the parameters used with the storeEvent action, the storeEventWithAck() action includes token and callback parameters. The token parameter specifies a user-defined string to be passed in that will be returned in the callback action. This allows the callback to perform different operations depending on the token value. In this way, a single callback action can perform different operations, eliminating the need to create separate callbacks for each operation. If the token parameter is not needed for the callback, it should be set to "" (an empty string).
The callback parameter specifies the callback action that handles the success or failure of the storeEventWithAck action.
If you want to avoid the overhead of receiving acknowledgments each time event data is added to a database table, use the storeEvent action. If your application needs to handle a failure during a call to the storeEvent action, it should call the setStoreErrorCallback action; for more information, see Handling data storing errors.
Storing non-event data
In order to store non-event data in a database, your application needs to use the Connection event’s storeData() action. The definition of the storeData() action is:
The tableName parameter specifies the name of the database table where you want to store the data.
The statementName parameter specifies the name of a StoreStatement that references a prepared statement or stored procedure. The storeStatement is created with the Connection event’s createStoreStatement action. See Creating and deleting store events for more information on creating a storeStatement. If you do not want to specify a prepared statement or stored procedure, the statementName parameter should be set to "" (an empty string).
The fields parameter specifies the column values to be stored.
To store an event and provide acknowledgment, implement the storeDataWithAck() action and a callback handler. The definition of the storeDataWithAck() action is:
In addition to the parameters used with the storeData() action, the storeDataWithAck() action includes token and callback parameters. The token parameter specifies a user-defined string to be passed in that will be returned in the callback action. This allows the callback to perform different operations defending on the token value. In this way, a single callback action can perform different operations, eliminating the need to create separate callbacks for each operation. If the token parameter is not needed for the callback, it should be set to "" (an empty string).
The callback parameter specifies the callback action that handles the success or failure of the storeDataWithAck() action. The acknowledgment callback string contains any errors reported as well as the returned token, an empty acknowledgment string indicates success.
If you do not have to take additional action each time a row of data is added to a database table, you can avoid the overhead of receiving acknowledgments by using the storeData() action. If your application needs to handle a failure during a call to the storeData() action, it should call the setStoreErrorCallback action; for more information, see Handling data storing errors.
Creating and deleting store events
If your application will use a prepared statement or a stored procedure in a store action (such as storeData or storeEvent) you need to first create a storeStatement with createStoreStatement action.
name - The name of the storeStatement instance that will be used in a store action. The name must be unique. Specifying a value for name is optional and if omitted, one will be created in the form Statement_1.
tableName - The name of the database table where the data will be written when the store action that uses the storeStatment is called.
statementString - The SQL string that will be used as a template when the store action that uses the storeStatement is called. You can use question mark characters to indicate replaceable parameters in the statement. For example, "insert into myTable(?,?,?) values(?,?,?)".
If you want to use a stored procedure, in the statementString enclose the name of the database’s stored procedure in curly brace characters ({ }) and use question mark characters (?) to indicate replaceable parameters. For example, "{call myStoredProcedure(?,?,?)}". Stored procedures used in this way can only take input parameters. The stored procedure must exist in the database.
inputTypes - Specifies the types that will be used as replaceable parameters in the statementString.
inputToNameMap - Specifies what data item should be used for each input parameter of the store statement. If storing data it would be the name from the dictionary of data to be stored. If storing events it would be the event field name. When you specify the dictionary, the integer is the position and the string is the data name. For example, you might specify the inputToNameMap parameter as follows:
callback - The action’s callback handler. The definition of the callback action should take the error message as the first string parameter followed by the storeStatement name.
If your application uses the storeData or storeEvent actions, you can use the setStoreErrorCallback action to handle failures. This is useful for applications that make a large number of store calls where high performance is important and acknowledgement for an individual store operation call is not required. A single setStoreErrorCallback action can handle the failure of multiple store calls. The setStoreErrorCallback action is defined as follows:
Calls to storeData and storeEvent actions return unique integer identifiers; use these identifiers in the setStoreErrorCallback action. The first integer specifies the indentifier of the first store action where an error occurred; the second integer specifies the indentifier of the last store action error. callback specifies the name of the user-defined error handling action.
Committing transactions
By default, the auto-commit behavior assumes the AutoCommit and StoreCommitInterval properties specified in the adapter’s configuration file and the open action are using the default values. If you want more control over when changes are committed to a database, set the openDatabase action’s autoCommit parameter to false and in your EPL code, manually commit data using the Connection event’s commitRequest action.
To commit a transaction manually
Create a callback action to handle the results of the commitRequest action.
Call the commitRequest() action of the Connection event (for the open database) with the name of the callback action.
The definitions for the two forms of the commitRequest action are:
To roll back a database transaction, your application should use the Connection event’s rollbackRequest action. If you want to use rollback actions, you need to turn autocommit off.
To roll back a transaction
Create a callback action to handle the results of the rollbackRequest action.
Call the rollbackRequest action of the Connection event (for the open database) with the name of the callback action.
The definitions for the two forms of the rollbackRequest action are:
To execute database commands, such as creating a table or SQL operations such as Delete and Update, use the Connection event’s runCommand action.
To run a command
Call the runCommand action of the Connection event (for the open database) with the a string containing the SQL command to execute and the name of the callback action.
Create a handler action for the runCommand() callback action.
The definitions for the two forms of the runCommand are:
An Apama application can execute three types of SQL queries on databases:
Standard query — An SQL query that you write in your EPL code. This is typically a simple query provided as a string when your EPL code initializes the query. The query string is used when the query is submitted to the database when your EPL code calls the action that starts the query. See Executing standard queries.
Prepared query — An SQL query that uses a “prepared statement” or “stored procedure”, both of which are stored in the database. Because they are stored in the database, prepared queries are more efficient than standard and named queries as they do not need to be compiled and destroyed each time they are run. Input parameters for prepared queries are not set during initialization. They are set after initialization, but before the query is submitted to the database when the query start action is called. See Prepared statements and Stored procedures.
Named query — An SQL query that you write in an XML file as part of the Apama project in Apama Plugin for Eclipse. Typically, you use a named query if you plan to use the query multiple times (as a template, supplying parameterized values). If the query is relatively complex, it is useful to separate it from your EPL code for readability. Your EPL code specifies the query template name and the template parameter names and values to use when it initializes the query. The template name and parameters are used when the query is submitted to the database when your EPL code calls the action that starts the query. See Named queries.
Executing standard queries
In order to execute a standard query, your application needs to implement the following steps:
Create a new Query event.
Initialize the query by calling the Query event’s initQuery action passing in the name of the database’s Connection event and the query string. The relevant init action should be called each time before calling the query’s start action.
Call the Query event’s setReturnType action to specify the return type. Apama recommends specifying the return type using one of the following constants:
Query.RESULT_EVENT
Query.RESULT_EVENT_HETERO
Query.NATIVE
Query.HISTORICAL
See Return Types below for more information on return types.
If the return type is Native, indicate the event type to be returned by specifying it with the Query event’s setEventType action.
The setEventType action is defined as:
action setEventType(string eventType)
In addition, you need to add mapping rules to the ADBC adapter’s configuration file for the event type being returned.
In addition, if the return type is Native, specify the database table column that stores the event’s timestamp with the Query event’s setTimeColumn action.
The setTimeColumn action is defined as:
action setTimeColumn(string timeColumn)
If the query will return a large number of results, call the Query event’s setBatchSize action passing in an integer setting the batch size.
If you set a batchsize, also use the Query event’s setBatchDoneCallback action passing in values for the token and callback parameters.
If the application needs to know the query’s result set schema, call the Query event’s setSchemaCallback action passing in the name of the handler action.
Call the Query event’s start action passing in the name of the handler action that will be called when the query completes.
Return Types:
NATIVE — This return type is most commonly used for playback. When a query is run, each row of the query will be passed through the IAF mapping rules and the matching event will be sent as-is to the correlator. The Native return type would not be used for general database queries.
In addition to specifying the Native return type, your query needs to specify the event type to be returned and the name of the database table’s column that contains the event’s time stamp. Specify the event by using the Query event’s setEventType action; specify the time column by using the Query event’s setTimeColumn action. You also need to add mapping rules for this event type to the ADBC adapter’s configuration file.
HISTORICAL — This return type is also used for playback. When a query is run, each row of the query will be passed through the IAF mapping rules and then the matching event will be “wrapped” in a container event. The container event will have a name based on that of the event name. For example a Tick event would be wrapped in a HistoricalTick event. Event wrapping allows events to be sent to the correlator without triggering application listeners. A separate user monitor can listen for wrapped events, modify the contained event, and reroute it such that application listeners can match on it. The Wrapped return type would not be used for general database queries.
RESULT_EVENT — This return type is used for general database queries. When a query is run, each row in the result set will be mapped to a dictionary in a generic ResultEvent. The ADBC adapter will generate a SchemaEvent containing the schema (name and type) of the fields in the result set of the query. The SchemaEvent will be sent first, before any ResultEvents.
The definition for ResultEvent is:
event ResultEvent {
integer messageId; // Unique id of query
string serviceId;
integer schemaId; // ResultSchema event schemaId to use with ResultEvent
dictionary <string, string> row; // Data
}
RESULT_EVENT_HETERO — This return type is intended for advanced database queries. It is not applicable to SQL databases. Some market databases support queries which can, in essence, return multiple tables. For example a market database might allow queries which return streams of both Tick and Quote data. For such databases multiple SchemaEvents would be generated indexed by id.
Stopping queries
The following action cancels all outstanding queries in the queue.
In order to provide compatibility for a wide number of database vendors, the ADBC adapter normally converts column names to lower case. However, if you want to execute complex queries where the _ADBCType or _ADBCTime are returned as part of the query rather than being specified using the setEventType and setTimeColumn actions on the query, you need to set the ColumnNameCase property in the ADBC adapter’s configuration file to unchanged.
Setting the ColumnNameCase property is done by manually editing the ColumnNameCase property to the configuration file.
To edit the ColumnNameCase property
In the Project Explorer, in the project’s Adapters node, expand the ODBC or JDBC adapter, and double-click the adapter instance to open it in the ADBC adapter editor.
Display the ADBC adapter editor’s XML source tab.
In the <transport> element, edit the ColumnNameCase property as follows:
When the ColumnNameCase property is set to unchanged, you can specify a query string in the following form:
string queryString := "SELECT *, 'Trade' AS _ADBCType FROM TradeTable
WHERE symbol = "ADL";
The other values for the ColumnNameCase property can be lower, (the default) and upper.
Prepared statements
Apama applications can use prepared statements when executing queries. Prepared statements have the following performance advantages over standard queries:
The query does not need to be re-parsed each time it is used.
The query allows for replaceable parameters.
Using a prepared statement
Note that PreparedQuery events support only ODBC/JDBC data types. Vendor-specific data types are not allowed.
To use a prepared statement
Create a new Query event.
Create a new PreparedQuery event.
Call the new PreparedQuery event’s init() action, passing in the database connection, the query string, the input types if using replaceable parameters and the output types if it will be used as a stored procedure.
conn — The name of the database’s Connection event.
queryString — The SQL query string; you can use question mark characters (?) to indicate replaceable parameters.
inputTypes — This is optional, but if you use replaceable parameters in the queryString, you need to specify the types that will be used in the query.
outputTypes — This is optional, but if the PreparedQuery event is to be used for a stored procedure and it uses output parameters, you need to specify the output types.
For example:
sequence<string> inputTypes := ["INTEGER","INTEGER"];
myPreparedQuery.init (
myConnection,
"SELECT * FROM mytable WHERE inventory > ? and inventory <?",
inputTypes, new sequence<string>);
Call the new PreparedQuery event’s create() action, passing in the name of the callback action.
In the callback action’s code, call the Query event’s initPreparedQuery() action (instead of the initQuery() action), passing in the name of the PreparedQuery event. See Executing standard queries. As with any query, the relevant init action should be called each time before calling the query’s start action.
Call the Query event’s setInputParams() action, passing in the values to be used for the replaceable parameters. This should always be called before starting a query that is using a prepared query.
The definition of the setInputParams() action is:
setInputParams(sequence<string> inputParams)
If you want to use NULL for the value of a replaceable parameter, use ADBC_NULL.
If necessary, call any of the other Query actions, such as setBatchSize(), as required.
Call the Query event’s start() action as you would when executing any other query. See Executing standard queries.
Stored procedures
Apama applications can use stored procedures when executing queries. Using stored procedures is similar to using prepared statements. The difference is that a stored procedure needs to specify the name of the stored procedure and the output types returned by the query.
Using a stored procedure
Queries in Apama applications use stored procedures by specifying the name of the stored procedure in a prepared statement’s query string.
To use a stored procedure
Create a new Query event.
Create a new PreparedQuery event.
Call the new PreparedQuery event’s init() action, passing in the database connection, the query string, the input types, and the output types.
conn — The name of the database’s Connection event.
queryString — The SQL query string; enclose the name of the database’s stored procedure in curly brace characters ({ }) and use question mark characters (?) to indicate replaceable parameters.
inputTypes — Specify the types that will be used for the replaceable parameters in the queryString.
outputTypes — Specify the types that will be used for the replaceable parameters in the result.
For example:
If a parameter is used as both an input and output type, it must be specified in both places.
If it is only an input type it must be specified as NULL in outputType.
If it is only an output type it must be specified as NULL in inputType.
Therefore, in the example above, the first parameter is just an input type; the second parameter is just an output type; and the third parameter is both an input and output type.
Call the new PreparedQuery event’s create() action, passing in the name of the callback action.
In the callback action’s code or once the callback action has been called, call the Query event’s initPreparedQuery() action instead of the initQuery() action, passing in the name of the PreparedQuery event. An error will be reported if the Query event’s initPreparedQuery is called before the PreparedQuery create callback has been called. See Executing standard queries.
Call the Query event’s setInputParams() action, passing in the values to be used for the replaceable parameters.
The definition of the setInputParams() action is:
setInputParams(sequence<string> inputParams)
If you want to use NULL for the value of a replaceable parameter, use ADBC_NULL.
If necessary, call any of the other Query actions, such as setBatchSize(), as required.
Call the Query event’s start() action as you would when executing any other query. See Executing standard queries.
Named queries
Apama applications can use named queries. Named queries are templates with parameterized values and are stored in Apama projects. Queries of this type provide advantages for queries that will be used multiple times. They also serve to keep the SQL query strings separate from the application’s EPL code.
To use a named query, your EPL code needs to specify the query template name and the template parameter names and values to use when it initializes the query. The template name and parameters are used when the query is submitted to the database.
You define a named query as a query template in the ADBC adapter’s ADBC-queryTemplates-SQL.xml file. This file contains some pre-built named queries:
findEarliest — Get the row with the earliest time (based on the stored event’s timestamp).
findLatest — Get the row with the latest time.
getCount — Get the number of rows in a table.
findAll — Get all the rows from a table.
findAllSorted — Get all the rows from a table ordered by column.
Using named queries
To use a named query
Create a new Query event.
Initialize the query by calling the Query event’s initNamedQuery() action, passing the name of the database’s Connection event, the name of the query template, and a dictionary<string, string> containing the names and values of the named query’s parameters.
Call the Query event’s setReturnType() action to specify the return type to be ResultEvent. When a query is run, each row in the result set will be mapped to a dictionary event field in a ResultEvent event.
Call the Query event’s setReturnEventCallback() action to specify the callback action that will handle the results returned by the query.
If the query will return a large number of events (on the order of thousands):
Call the Query event’s setBatchSize() action passing an integer that sets the batch size. The query returns results in batches of the specified size.
Call the Query event’s setBatchDoneCallback() action passing the name of the handler action.
Define the setBatchDoneCallback() action to define what to do when a batch is complete. You must call the Query event’s getNextBatch() action to continue receiving the query results. The batch size for the next batch is set by passing an integer parameter for the batch size. You could also call the stop action to stop the query, rather than continuing to receive batches of data.
Call the Query event’s start() action passing the name of the handler action that will be called when the query completes.
Create the callback action that you specified in Step 4, to handle the results returned by the query.
Each row of data that matches the query results in a call to the callback action, returning the row results in a parameter of ResultEvent type. The ResultEvent type contains a dictionary field that contains the row data.
Create the action that specifies what to do when the query completes (when all results are returned).
The following example uses the initNamedQuery() action call to initialize the query, specifying the findEarliest named query and stock_tables as the value for the named query’s TABLE_NAME parameter.
using com.apama.database.Connection;
using com.apama.database.Query;
using com.apama.database.ResultEvent;
monitor ADBCexample {
Connection conn;
Query query;
string serviceId := "com.apama.adbc.JDBC_INSTANCE_1";
string dbUrl := "jdbc:mysql://127.0.0.1:3306/exampledb";
string user := "root";
string password := "mysql";
string queryString := "SELECT * FROM sys.tables";
string tableName := "stock_table";
dictionary<string,string> paramTable :=
{"TABLE_NAME":tableName,"TIME_COLUMN_NAME":"tbd"};
action onload() {
conn.openDatabase(serviceId, dbUrl, user, password, "",
handleOpenDatabase);
}
action handleOpenDatabase (Connection conn, string error){
if error.length() != 0 {
log "Error opening database : " + error at ERROR;
}
else {
log "Database is open." at INFO;
runQuery();
}
}
action runQuery() {
query.initNamedQuery(conn, "findEarliest", paramTable);
query.setReturnType("ResultEvent");
query.setResultEventCallback("token", handleResultEvent);
query.start(handleQueryComplete);
}
action handleResultEvent(Query q, ResultEvent result,string token) {
log result.toString() at INFO;
}
action handleQueryComplete(Query query, string error,
integer eventCount, float lastEventTime) {
if error.length() != 0 {
log "Error running query '" + queryString + "': " +
error at ERROR;
}
else {
log " Query '" + queryString + "' successfully run." at INFO;
log " Total events: " + eventCount.toString() at INFO;
if lastEventTime > 0.0 {
log " Last Event Time: " + lastEventTime.toString()
at INFO;
}
}
conn.closeDatabase(handleCloseDatabase);
}
action handleCloseDatabase(Connection conn, string error) {
if error.length() != 0 {
log "Error closing database : " + error at ERROR;
}
else {
log "Database closed." at INFO;
}
}
}
Creating named queries
Each named query in the ADBC-queryTemplates-SQL.xml file is defined in an XML <query> element. Each <query> element has the following attributes:
name — The name of the query.
description — A short description of the query.
implementationFunction — The substitution function that the adapter uses to process the named query. The substitution function allows you to specify tokens that are replaced by parameters with matching names.
inputString — A string that contains the substitution tokens you want to replace with values specified as parameters.
A <query> element can also have one or more optional <parameter> child elements. Each <parameter> element has the following attributes:
description — A short description of the parameter.
name — The name of the parameter.
type — The data type of the parameter.
default — The default value of the parameter.
As an example, the following XML code in the ADBC-queryTemplates-SQL.xml file defines the pre-built findEarliest named query. The query returns the row with the earliest time.
<query name="findEarliest" description="Get the row with the earliest time." implementationFunction="substitution" inputString="select * from ${TABLE_NAME} order by ${TIME_COLUMN_NAME}
asc limit 1"><parameter description="Name of a table to query" name="TABLE_NAME" type="String" default=""/><parameter description="Name of the time column" name="TIME_COLUMN_NAME" type="String" default="time"/></query>
To create a named query
In the Project Explorer, expand the project’s Adapters node and open the adapter folder.
Double-click the instance configuration file to open it in the adapter editor.
In the adapter editor, select the Advanced tab.
Click the ADBC-queryTemplates-SQL.xml file to open it.
Select the Design tab.
On the Design tab, right-click the namedQuery element and select Add Child > New Element.
In the New Element dialog, type query, then click OK. A new query row is added to the list.
For each of the four attributes (name, description, implementationFunction, inputString):
Right-click the query element you have added, and select Add Attribute > New Attribute.
In the New Attribute dialog, provide a Name and a Value for the attribute.
If you want the query to use input parameters, for each parameter:
Right-click the query element and select Add Child > New Element.
In the New Element dialog, type parameter, then click OK.
Create the following attributes for each parameter:
description
name
type
default
Save the project’s version of the query template file.
The Visual Event Mapper
Note: The Visual Event Mapper is no longer available for ODBC data sources.
When you add or open an instance of the ADBC Adapter, the adapter editor provides a Visual Event Mapper. The Event Mapper is available by selecting the Event Mapping tab. With the Event Mapper you specify an Apama event type and a table in an existing JDBC database. When you save the adapter configuration file, Apama Plugin for Eclipse creates the rules that provide the mapping between the fields in the event and the columns in the database. The mapping rules are stored in the adapter instance’s configuration file.
The Generate Store Monitors option in the Visual Event Mapper specifies whether or not Apama Plugin for Eclipse generates all the necessary EPL code for monitors that listen for events of the specified types as well as for the EPL code that interacts with the database – opening the database, checking the adapter status, storing event data, etc. This is the default setting. If you turn this option off, you need to write the EPL code for event listeners and for interacting with the database.
The Auto Start Events option in the Event Mapper specifies whether or not Apama Plugin for Eclipse generates events that cause Apama Plugin for Eclipse to automatically start saving event data when the application is launched. If you turn this option off, your application needs to manually send a StartStoreConfiguration event in order to start saving data.
Using the Visual Event Mapper
ADBC uses the SQL driver to perform the conversion between Apama types and SQL (JDBC) types. Any restrictions are due to the SQL database vendor and the SQL driver being used.
To map an Apama event to a table in a database
Add a new instance of the ADBC Adapter or open an existing instance and select the adapter editor’s Event Mapping tab.
If you want Apama Plugin for Eclipse to automatically generate an EPL monitor to listen for events of the specified type, make sure the Generate Store Monitors option is enabled; this is the default setting. In addition to generating all the necessary EPL code for monitors that listen for events of the specified types, all the EPL code that interacts with the database is generated: opening the database, checking the adapter status, storing event data, etc. This setting is useful if your application does not need to guarantee that each event is persisted. The generated monitor provides a best effort storage implementation suitable for storing data to be analyzed in tools like Analyst Studio. The generated monitor does not perform any filtering so all events of the type specified will be stored.
If your application needs to perform filtering of the events or needs to guarantee that each event will be persisted, you should disable Generate Store Monitors option and manually write the required code for the EPL monitors and for interacting with the database.
Make sure there is a check mark in the Auto Start check box (this is the default) if you want to start saving event data immediately when you launch the project. If you clear the check mark in the Auto Start check box, your application will need to manually send a StartStoreConfiguration event in order to start storing events.
In the adapter editor, click the Add button. The Event Persistence Configuration dialog opens.
In the Event Persistence Configuration dialog, click the Browse button next to the Event field. The Event Type Selection dialog opens, displaying the available event types you can select from. Only events that can be emitted are shown; events that contain fields with contexts or actions are not displayed.
In the Event Type Selection dialog, select the event type you want to map as follows:
In the Event Type Selection field, enter the name of the event. As you type, event types that match what you enter are shown in the Matching Items list. When you select an event, the full name is shown on the dialog’s status line. You can turn off this display with the dialog’s Down Arrow menu icon ().
In the Matching Items list, select the name of the event type you want to map. The name of the EPL file that defines the selected event is displayed in the status area at the bottom of the dialog.
Click OK.
In the Event Persistence Configuration dialog, click the Browse button next to the Database table field. The Database Table Selection dialog opens.
In the Database Table Selection dialog, select the database table to which you want to map the event’s fields as follows:
In the Database Server Details section, specify the DB URL, User Name, and Password. By default, the DB URL uses the value used in the adapter configuration settings. You can change the name of the database by un-checking the check box and entering a new name. (Note, you cannot change the type of database.)
Click Connect to access the database.
Select the name of the table from the Matching Items list or enter text in the Database Table Selection field. As you type, table names that match what you enter are shown in the Matching Items list. When you select a table, its name is also shown on the dialog’s status line. You can turn off this display with the dialog’s Down Arrow menu icon ().
In the Matching Items list, select the name of the database table where you want to store the event data.
Click OK.
In the Event Persistence Configuration dialog, click OK. The adapter editor display is updated to show the name of the event type and the database table in the Event section. The Mapping Rules section displays lists for Event and Database Table.
For each event field you want to store in the Event list click on the field and draw a line to the desired column in the Database Table list.
When you save the adapter instance configuration, mapping rules are generated that specify the associations between event fields and database columns. A monitor that listens for events of the specified type is also generated. The monitor allows the Apama application to manage when the events are written to the database.
Playback
If event data is stored in a database, you can play back the events through the correlator using the Apama Data Player in Apama Plugin for Eclipse. The Data Player consists of the Query Editor and the Data Player control. In the Query Editor, you create and modify queries in order to specify what event data you want to play back. The Data Player control allows you to specify what query to use and how fast to play back the event data.
For full information on the Data Player, see Using the Data Player in Using Apama with Apama Plugin for Eclipse.
Command line tools
When you have stored event data in a database and created queries in Apama Plugin for Eclipse, you can also launch a playback session using the Data Player command line tool, adbc_management.
In Apama 4.1 and earlier, Apama captured data streaming through the correlator into proprietary .sim files. These files consist of comma-delimited values. You can use the Apama’s Data Player in Apama Plugin for Eclipse to play back event data from existing .sim files. Note, however, that the ADBC does not write data in .sim format.
Apama .sim files contain string versions of events and can also contain an optional header that specifies the default time zone for the series. The time-zone identifiers can be any supported by Java. The format of the events contained in a .sim file is:
timestamp — a float specifying UTC seconds since 01/01/1970.
event origin — a string specifying whether the event is an internal or external event.
event — a stringified version of the event itself.
Elements of the exported event are separated by commas.
The following is an example of an external event from a .sim file (each event is stored on a single line, here they are shown on separate lines for clarity):